using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using au.Swindles.LoanDataService.ObjectLayer;
using System.Data.SqlClient;
using System.Text;

namespace au.Swindles.LoanDataService.AdapterLayer
{
    public static class ApplicationAdapter
    {

        private static string getConnectionString()
        {
            return ConfigurationManager.AppSettings["DBConnection"];
        }

        private static SqlParameter getSqlInputParameter(string pParameterName, object pParameterValue, SqlDbType pParameterType)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = pParameterName;
            param.Direction = ParameterDirection.Input;
            param.SqlDbType = pParameterType;
            param.Value = pParameterValue;

            return param;
        }

        private static SqlParameter getSqlOutputParameter(string pParameterName, SqlDbType pParameterType)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = pParameterName;
            param.Direction = ParameterDirection.Output;
            param.SqlDbType = pParameterType;

            return param;
        }

        public static Application createApplication(Application pApplication)
        {
            Application app = pApplication;
            SqlConnection connection = new SqlConnection(getConnectionString());
            StringBuilder commandtext = new StringBuilder();
            try
            {
                commandtext.Append(" Insert [dbo].[tblApplication]");
                commandtext.Append("(CustomerID, QuoteID , AssetValue , AnnualIncome , AnnualExpense , ApplicationStatus , StatusUpdateDate , StatusUpdateUserID , Comments )");
                commandtext.Append(" VALUES (@pCustomerID, @pQuoteID , @pAssetValue , @pAnnualIncome , @pAnnualExpense , @pApplicationStatus , @pStatusUpdateDate , @pStatusUpdateUserID , @pComments )");
                commandtext.Append(" SET @pApplicationID = SCOPE_IDENTITY()");


                SqlCommand command = new SqlCommand(commandtext.ToString(), connection);
                command.Parameters.Add(getSqlInputParameter("@pCustomerID", pApplication.CustomerID, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pQuoteID", pApplication.QuoteID, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pAssetValue", pApplication.AssetValue, SqlDbType.Decimal));
                command.Parameters.Add(getSqlInputParameter("@pAnnualIncome", pApplication.AnnualIncome, SqlDbType.Decimal));
                command.Parameters.Add(getSqlInputParameter("@pAnnualExpense", pApplication.AnnualExpense, SqlDbType.Decimal));
                command.Parameters.Add(getSqlInputParameter("@pApplicationStatus", pApplication.ApplicationStatus, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pStatusUpdateDate", pApplication.StatusUpdateDate, SqlDbType.DateTime));
                command.Parameters.Add(getSqlInputParameter("@pStatusUpdateUserID", pApplication.StatusUpdateUserID, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pComments", pApplication.Comments, SqlDbType.VarChar));
                command.Parameters.Add(getSqlOutputParameter("@pApplicationID",SqlDbType.Int));

                connection.Open();

                command.ExecuteNonQuery();
                app.ApplicationID = Convert.ToInt32(command.Parameters[9].Value);

                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw ex;
            }
            return app;

        }

        public static void updateLoanApplication(Application pApplication)
        {
            SqlConnection connection = new SqlConnection(getConnectionString());
            StringBuilder commandtext = new StringBuilder();
            try
            {
                commandtext.Append("UPDATE [dbo].[tblApplication]");
                commandtext.Append("(CustomerID, QuoteID , AssetValue , AnnualIncome , AnnualExpense , ApplicationStatus , StatusUpdateDate , StatusUpdateUserID , Comments )");
                commandtext.Append("VALUES (@pCustomerID, @pQuoteID , @pAssetValue , @pAnnualIncome , @pAnnualExpense , @pApplicationStatus , @pStatusUpdateDate , @pStatusUpdateUserID , @pComments )");
                commandtext.Append(" WHERE ApplicationID = @pApplicationID");
                SqlCommand command = new SqlCommand(commandtext.ToString(), connection);

                command.Parameters.Add(getSqlInputParameter("@pCustomerID", pApplication.CustomerID, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pQuoteID", pApplication.QuoteID, SqlDbType.Int));
                command.Parameters.Add(getSqlInputParameter("@pAssetValue", pApplication.AssetValue, SqlDbType.Decimal));
                command.Parameters.Add(getSqlInputParameter("@pAnnualIncome", pApplication.AnnualIncome, SqlDbType.Decimal));
                command.Parameters.Add(getSqlInputParameter("@pAnnualExpense", pApplication.AnnualExpense, SqlDbType.Decimal));
                command.Parameters.Add(getSqlInputParameter("@pApplicationStatus", pApplication.ApplicationStatus, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pStatusUpdateDate", pApplication.StatusUpdateDate, SqlDbType.DateTime));
                command.Parameters.Add(getSqlInputParameter("@pStatusUpdateUserID", pApplication.StatusUpdateUserID, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pComments", pApplication.Comments, SqlDbType.VarChar));
                command.Parameters.Add(getSqlInputParameter("@pApplicationID",pApplication.ApplicationID,SqlDbType.Int));
                
                connection.Open();

                command.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                throw ex;
            }
        }

        public static List<Application> getLoanApplicationList()
        {
            List<Application> appList = new List<Application>();
            SqlConnection connection = new SqlConnection(getConnectionString());
            StringBuilder commandtext = new StringBuilder();
            try
            {
                commandtext.Append("SELECT * FROM [dbo].[tblApplication]");

                SqlCommand command = new SqlCommand(commandtext.ToString(), connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    appList.Add(new Application(Utilities.getInt32(reader["ApplicationID"])
                                                    , Utilities.getInt32(reader["CustomerID"])
                                                    , Utilities.getInt32(reader["QuoteID"])
                                                    , Utilities.getDecimal(reader["AssetValue"])
                                                    , Utilities.getDecimal(reader["AnnualIncome"])
                                                    , Utilities.getDecimal(reader["AnnualExpense"])
                                                    , Utilities.getString(reader["ApplicationStatus"])
                                                    , Utilities.getDateTime(reader["StatusUpdateDate"])
                                                    , Utilities.getInt32(reader["StatusUpdateUserID"])
                                                    , Utilities.getString(reader["Comments"])
                                                   ));
                }

                connection.Close();
                return appList;
            }
            catch (Exception)
            {
                connection.Close();
                return null;
            }
        }


    }
}
